﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Data.Common;

namespace DBHelper
{
    public class DbHelperSQL
    {
      
       public static  SqlConnection conn = null;
       static string connstring = "server=.;database=GraduationDataBase;uid=sa;pwd=123;MultipleActiveResultSets=true";
       public DbHelperSQL()
        {
            GetConn();
        }
        public static SqlConnection GetConn()
        {
            if (conn == null)
            {
               
                conn = new SqlConnection(connstring);
            }
            if (conn.State == ConnectionState.Closed)
            {
                 conn.Open();
            }
            return conn;
        }

        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        public static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>表</returns>
        public static  DataSet Query(string sql)
        {
            GetConn();
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }

        public static object ExecuteScalar(string sql)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand(sql,conn);
            return cmd.ExecuteScalar();
        
        }

        /// <summary>
        /// 查询单列数据
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>返回集合对像</returns>

        public static  ArrayList QueryList(string sql)
        {
            GetConn();
            SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            ArrayList al = new ArrayList();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                al.Add(ds.Tables[0].Rows[i][0]);
            }
            return al;
        }
        
        public static int ExcuteNoQuery(string sql)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand(sql,conn);
            int k = cmd.ExecuteNonQuery();
            return k;
        }

        public static SqlDataReader ExecuteQuery(string sql)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand();//sql语句命令执行对象
            cmd.Connection = conn;
            cmd.CommandText = sql;//待执行的sql脚本
            SqlDataReader dr = cmd.ExecuteReader();//执行结果放入datareader
            return dr;
        }
        public static int ExcuteNoQuery(string sql, SqlParameter[] paras)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand();//sql语句命令执行对象
            cmd.Connection =conn;
            cmd.CommandText = sql;

            foreach (var para in paras)
            {
                cmd.Parameters.Add(para);
            }

            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
           
                using (SqlCommand cmd = new SqlCommand(SQLString, conn))
                {
                    try
                    {
                        
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        conn.Close();
                        throw e;
                    }
                }
            
        }
       
    }
}
